using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class OrderDetailDAO : IOrderDetailDAO

    {
        private const string SQL_SELECT_ORDERDETAIL = "SELECT * FROM OrderDetails";

        private const string SQL_SELECT_ORDERDETAIL_BY_ID = "SELECT * FROM OrderDetails WHERE OrderID = @OrderID";

        private const string SQL_INSERT_ORDERDETAIL = "INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, Quantity, Total) " +
                                                    "VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, @Total)";

        // Parameters
        private const string PARM_ORDER_ID = "@OrderID";
        private const string PARM_PRODUCT_ID = "@ProductID";
        private const string PARM_UNIT_PRICE = "@UnitPrice";
        private const string PARM_QUANTITY = "@Quantity";
        private const string PARM_TOTAL = "@Total";



        #region IOrderDetailDAO Members

        public IList<OrderDetailInfo> GetAll()
        {
            IList<OrderDetailInfo> orderdetails = new List<OrderDetailInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ORDERDETAIL, null))
            {
                while (reader.Read())
                {
                    OrderDetailInfo orderdetail = new OrderDetailInfo();
                    orderdetail.OrderID = reader.GetInt32(0);
                    orderdetail.ProductID = reader.GetInt32(1);
                    orderdetail.UnitPrice = reader.GetDecimal(2);
                    orderdetail.Quantity = reader.GetInt16(3);                    
                    orderdetail.Total = reader.GetDecimal(4);
                    orderdetails.Add(orderdetail);
                }
            }
            return orderdetails;
        }

        public IList<OrderDetailInfo> GetByID(string orderID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_ORDER_ID, SqlDbType.Int) };
            parms[0].Value = int.Parse(orderID);

            IList<OrderDetailInfo> orderdetails = new List<OrderDetailInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ORDERDETAIL_BY_ID, parms))
            {
                while (reader.Read())
                {
                    OrderDetailInfo orderdetail = new OrderDetailInfo();
                    orderdetail.OrderID = reader.GetInt32(0);
                    orderdetail.ProductID = reader.GetInt32(1);                    
                    orderdetail.UnitPrice = reader.GetDecimal(2);
                    orderdetail.Quantity = reader.GetInt16(3);                    
                    orderdetail.Total = reader.GetDecimal(4);                    
                    orderdetail.ProductName = new ProductDAO().GetByID(orderdetail.ProductID.ToString()).ProductName;
                    orderdetails.Add(orderdetail);                  
                }
            }
            return orderdetails;
        }

        public bool Insert(OrderDetailInfo orderDetail)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
								        new SqlParameter(PARM_ORDER_ID, SqlDbType.Int),
								        new SqlParameter(PARM_PRODUCT_ID, SqlDbType.Int),
                                        new SqlParameter(PARM_UNIT_PRICE, SqlDbType.Money),
                                        new SqlParameter(PARM_QUANTITY, SqlDbType.SmallInt),                                                                                
                                        new SqlParameter(PARM_TOTAL, SqlDbType.Decimal) };

            parms[0].Value = orderDetail.OrderID;
            parms[1].Value = orderDetail.ProductID;
            parms[2].Value = orderDetail.UnitPrice;
            parms[3].Value = orderDetail.Quantity;                        
            parms[4].Value = orderDetail.Total;           

            try
            {
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                int result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_ORDERDETAIL, parms);
                //SQLDBHelper.SetNewIdentity(conn, orderDetail.OrderID);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
